﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using LeadVinaglue.Models;
using LeadVinaglue.Models.ViewPages;
using System.Data.SqlClient;
using LeadVinaglue.Helpers;
using System.Data;

namespace LeadVinaglue.Repository
{
    public class DashboardManagementRepository : BaseManagementRepository
    {
        LeadVinaglueEntities entities = new LeadVinaglueEntities();

        #region Members
        public DashboardManagementModel GetModelByUser(int userId, string pageType, string roleName)
        {
            DashboardManagementModel model = new DashboardManagementModel();

            DateTime startDate = Utility.LeadTools.StartOfDay(DateTime.Now);
            DateTime endDate = Utility.LeadTools.EndOfDay(DateTime.Now);

            model.HeaderView = GetHeader();
            model.MenuItem = GetMenuItems(roleName, pageType);

            model.ProductList = GetCalendarEventsByUser(userId, startDate, endDate);
            model.CategoryList = GetStatistictProductByUserCategory(userId);

            model.LastUpdated = DateTime.Now.ToString("hh:mm tt");
            model.CurrentDate = DateTime.Now.ToString("dd-MM-yyyy");

            return model;
        }
        public DashboardManagementModel GetModelByUser(int userId, DateTime currentDate)
        {
            DashboardManagementModel model = new DashboardManagementModel();

            DateTime startDate = Utility.LeadTools.StartOfDay(currentDate);
            DateTime endDate = Utility.LeadTools.EndOfDay(currentDate);

            model.ProductList = GetCalendarEventsByUser(userId, startDate, endDate);

            model.CurrentDate = currentDate.ToString("dd-MM-yyyy");

            return model;
        }
        #endregion

        public DashboardManagementModel GetModel(string pageType, string roleName, string type)
        {
            DashboardManagementModel model = new DashboardManagementModel();

            DateTime startDate = Utility.LeadTools.StartOfDay(DateTime.Now);
            DateTime endDate = Utility.LeadTools.EndOfDay(DateTime.Now);

            model.HeaderView = GetHeader();
            model.MenuItem = GetMenuItems(roleName, pageType);

            if (type.ToUpper() == "PRODUCT")
            {
                model.ProductList = GetCalendarEvents(startDate, endDate, type);
                model.CategoryList = GetStatisticProductByCategory();
            }
            else
            {
                model.BannerList = GetCalendarEvents(startDate, endDate, type);
                model.OptionList = GetStatisticBTBy("option");
                model.GroupList = GetStatisticBTBy("group");
                model.PositionList = GetStatisticBTBy("position");
                model.PaymentList = GetStatisticBTBy("payment");
            }

            model.LastUpdated = DateTime.Now.ToString("hh:mm tt");
            model.CurrentDate = DateTime.Now.ToString("dd-MM-yyyy");

            return model;
        }
                
        public DashboardManagementModel GetModelPAApproved(string pageType, string roleName, int pageSize, int id)
        {
            DashboardManagementModel model = new DashboardManagementModel();

            if (id > 0)
            {
                ProductManagementRepository pm = new ProductManagementRepository();

                pm.Approved(id, 1);
            }

            model.Products = GetPAList(pageSize, false);
            model.ProductApproved = GetPAList(pageSize, true);

            return model;
        }  
        
        public DashboardManagementModel GetModelPAApproved(string pageType, string roleName, int pageSize)
        {
            DashboardManagementModel model = new DashboardManagementModel();

            model.Products = GetPAList(pageSize, false);
            model.ProductApproved = GetPAList(pageSize, true);

            return model;
        } 
                
        public DashboardManagementModel GetModelPADelete(string pageType, string roleName, int pageSize, int id)
        {
            DashboardManagementModel model = new DashboardManagementModel();

            if (id > 0)
            {
                ProductManagementRepository pm = new ProductManagementRepository();
                pm.Delete(id);
            }

            model.Products = GetPAList(pageSize, false);

            return model;
        }        
       
        public DashboardManagementModel GetModelPA(string pageType, string roleName, int pageSize)
        {
            DashboardManagementModel model = new DashboardManagementModel();

            DateTime startDate = Utility.LeadTools.StartOfDay(DateTime.Now);
            DateTime endDate = Utility.LeadTools.EndOfDay(DateTime.Now);

            model.HeaderView = GetHeader();
            model.MenuItem = GetMenuItems(roleName, pageType);

            model.Products = GetPAList(pageSize, false);
            model.ProductApproved = GetPAList(pageSize, true);

            model.LastUpdated = DateTime.Now.ToString("hh:mm tt");
            model.CurrentDate = DateTime.Now.ToString("dd-MM-yyyy");

            return model;
        }

        public DashboardManagementModel GetModel(DateTime currentDate, string type)
        {
            DashboardManagementModel model = new DashboardManagementModel();

            DateTime startDate = Utility.LeadTools.StartOfDay(currentDate);
            DateTime endDate = Utility.LeadTools.EndOfDay(currentDate);

            if (type.ToUpper() == "PRODUCT")
            {
                model.ProductList = GetCalendarEvents(startDate, endDate, type);
            }
            else
            {
                model.BannerList = GetCalendarEvents(startDate, endDate, type);
            }

            model.CurrentDate = currentDate.ToString("dd-MM-yyyy");

            return model;
        }

        public DashboardManagementModel GetModel(string type)
        {
            DashboardManagementModel model = new DashboardManagementModel();

            if (type.ToUpper() == "PRODUCT")
            {
                model.CategoryList = GetStatisticProductByCategory();
                model.StateList = GetStatisticProductByState();
            }
            else
            {
                model.OptionList = GetStatisticBTBy("option");
                model.GroupList = GetStatisticBTBy("group");
                model.PositionList = GetStatisticBTBy("position");
                model.PaymentList = GetStatisticBTBy("payment");
            }

            model.LastUpdated = DateTime.Now.ToString("hh:mm tt");

            return model;
        }
        
        #region #Private functions
        private List<MilestoneModel> GetStatistictProductByUserCategory(int userId)
        {
            List<MilestoneModel> list = new List<MilestoneModel>();
            SqlParameter paramUser = new SqlParameter("UserId", SqlDbType.Int);
            paramUser.Value = userId;

            // Execute a query
            using (SqlDataReader item = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.StoredProcedure, "GetStatisticCategoryByUser", paramUser))
            {
                while (item.Read())
                {
                    MilestoneModel model = new MilestoneModel();

                    model.ID = item.GetInt32(0).ToString();
                    model.Name = item.GetString(1);
                    model.Today = item.GetInt32(2);
                    model.Yesterday = item.GetInt32(3);
                    model.SevenDay = item.GetInt32(4);
                    model.ThirdtyDay = item.GetInt32(5);
                    model.SixtyDay = item.GetInt32(6);
                    model.Total = item.GetInt32(7);

                    list.Add(model);
                }
            }

            if (list.Count > 0)
            {
                MilestoneModel model = new MilestoneModel();
                model.Name = "Tổng";
                model.Today = list.Sum(c => c.Today);
                model.Yesterday = list.Sum(c => c.Yesterday);
                model.SevenDay = list.Sum(c => c.SevenDay);
                model.ThirdtyDay = list.Sum(c => c.ThirdtyDay);
                model.SixtyDay = list.Sum(c => c.SixtyDay);
                model.Total = list.Sum(c => c.Total);

                list.Add(model);
            }

            return list;
        }
        private List<MilestoneModel> GetStatisticProductByCategory()
        {
            List<MilestoneModel> list = new List<MilestoneModel>();

            // Execute a query
            using (SqlDataReader item = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.StoredProcedure, "GetStatisticCategory"))
            {
                while (item.Read())
                {
                    MilestoneModel model = new MilestoneModel();

                    model.ID = item.GetInt32(0).ToString();
                    model.Name = item.GetString(1);
                    model.Today = item.GetInt32(2);
                    model.Yesterday = item.GetInt32(3);
                    model.SevenDay = item.GetInt32(4);
                    model.ThirdtyDay = item.GetInt32(5);
                    model.SixtyDay = item.GetInt32(6);
                    model.Total = item.GetInt32(7);

                    list.Add(model);
                }
            }

            if (list.Count > 0)
            {
                MilestoneModel model = new MilestoneModel();
                model.Name = "Tổng";
                model.Today = list.Sum(c => c.Today);
                model.Yesterday = list.Sum(c => c.Yesterday);
                model.SevenDay = list.Sum(c => c.SevenDay);
                model.ThirdtyDay = list.Sum(c => c.ThirdtyDay);
                model.SixtyDay = list.Sum(c => c.SixtyDay);
                model.Total = list.Sum(c => c.Total);

                list.Add(model);
            }

            return list;
        }

        private List<MilestoneModel> GetStatisticProductByUserState(int userId)
        {
            List<MilestoneModel> list = new List<MilestoneModel>();
            SqlParameter paramUser = new SqlParameter("UserId", SqlDbType.Int);
            paramUser.Value = userId;
            // Execute a query
            using (SqlDataReader item = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.StoredProcedure, "GetStatisticStateByUser", paramUser))
            {
                while (item.Read())
                {
                    MilestoneModel model = new MilestoneModel();

                    model.ID = item.GetString(0);
                    model.Name = item.GetString(1);
                    model.Today = item.GetInt32(2);
                    model.Yesterday = item.GetInt32(3);
                    model.SevenDay = item.GetInt32(4);
                    model.ThirdtyDay = item.GetInt32(5);
                    model.SixtyDay = item.GetInt32(6);
                    model.Total = item.GetInt32(7);

                    list.Add(model);
                }
            }

            if (list.Count > 0)
            {
                MilestoneModel model = new MilestoneModel();
                model.Name = "Tổng";
                model.Today = list.Sum(c => c.Today);
                model.Yesterday = list.Sum(c => c.Yesterday);
                model.SevenDay = list.Sum(c => c.SevenDay);
                model.ThirdtyDay = list.Sum(c => c.ThirdtyDay);
                model.SixtyDay = list.Sum(c => c.SixtyDay);
                model.Total = list.Sum(c => c.Total);

                list.Add(model);
            }

            return list;
        }

        private List<MilestoneModel> GetStatisticProductByState()
        {
            List<MilestoneModel> list = new List<MilestoneModel>();

            // Execute a query
            using (SqlDataReader item = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.StoredProcedure, "GetStatisticState"))
            {
                while (item.Read())
                {
                    MilestoneModel model = new MilestoneModel();

                    model.ID = item.GetString(0);
                    model.Name = item.GetString(1);
                    model.Today = item.GetInt32(2);
                    model.Yesterday = item.GetInt32(3);
                    model.SevenDay = item.GetInt32(4);
                    model.ThirdtyDay = item.GetInt32(5);
                    model.SixtyDay = item.GetInt32(6);
                    model.Total = item.GetInt32(7);

                    list.Add(model);
                }
            }

            if (list.Count > 0)
            {
                MilestoneModel model = new MilestoneModel();
                model.Name = "Tổng";
                model.Today = list.Sum(c => c.Today);
                model.Yesterday = list.Sum(c => c.Yesterday);
                model.SevenDay = list.Sum(c => c.SevenDay);
                model.ThirdtyDay = list.Sum(c => c.ThirdtyDay);
                model.SixtyDay = list.Sum(c => c.SixtyDay);
                model.Total = list.Sum(c => c.Total);

                list.Add(model);
            }

            return list;
        }

        private List<MilestoneModel> GetStatisticBTBy(string optionName)
        {
            List<MilestoneModel> list = new List<MilestoneModel>();
            string storeName = string.Empty;

            switch (optionName.ToUpper())
            {
                case "GROUP":
                    {
                        storeName = "GetStatisticBTByGroup";
                        break;
                    }
                case "POSITION":
                    {
                        storeName = "GetStatisticBTByPosition";
                        break;
                    }
                case "OPTION":
                    {
                        storeName = "GetStatisticBTByOption";
                        break;
                    }
                case "PAYMENT":
                    {
                        storeName = "GetStatisticBTByPayment";
                        break;
                    }
                default:
                    {
                        storeName = "GetStatisticBTByOption";
                        break;
                    }
            }

            // Execute a query
            using (SqlDataReader item = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.StoredProcedure, storeName))
            {
                while (item.Read())
                {
                    MilestoneModel model = new MilestoneModel();

                    if (optionName.ToUpper() == "GROUP" || optionName.ToUpper() == "POSITION")
                        model.ID = item.GetString(0);
                    else
                        model.ID = item.GetInt32(0).ToString();

                    model.Name = item.GetString(1);
                    model.Today = item.GetInt32(2);
                    model.Yesterday = item.GetInt32(3);
                    model.SevenDay = item.GetInt32(4);
                    model.ThirdtyDay = item.GetInt32(5);
                    model.SixtyDay = item.GetInt32(6);
                    model.Total = item.GetInt32(7);

                    list.Add(model);
                }
            }

            if (list.Count > 0)
            {
                MilestoneModel model = new MilestoneModel();
                model.Name = "Tổng";
                model.Today = list.Sum(c => c.Today);
                model.Yesterday = list.Sum(c => c.Yesterday);
                model.SevenDay = list.Sum(c => c.SevenDay);
                model.ThirdtyDay = list.Sum(c => c.ThirdtyDay);
                model.SixtyDay = list.Sum(c => c.SixtyDay);
                model.Total = list.Sum(c => c.Total);

                list.Add(model);
            }

            return list;
        }

        private List<CalendarEvent> GetCalendarEventsByUser(int userId, DateTime startDate, DateTime endDate)
        {
            List<CalendarEvent> list = new List<CalendarEvent>();
            string storeName = string.Empty;

            storeName = "GetAllProductCalendarByUser";

            SqlParameter paramUser = new SqlParameter("UserId", SqlDbType.Int);
            SqlParameter paramStartDate = new SqlParameter("StartDate", SqlDbType.DateTime);
            SqlParameter paramEndDate = new SqlParameter("EndDate", SqlDbType.DateTime);

            paramUser.Value = userId;
            paramStartDate.Value = startDate;
            paramEndDate.Value = endDate;

            // Execute a query
            using (SqlDataReader item = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.StoredProcedure, storeName, paramUser, paramStartDate, paramEndDate))
            {
                while (item.Read())
                {
                    CalendarEvent model = new CalendarEvent();

                    model.ID = item.GetInt32(0);
                    if (!item.IsDBNull(1))
                        model.Title = item.GetString(1);
                    if (!item.IsDBNull(2))
                        model.Reviewed = item.GetInt32(2);
                    if (!item.IsDBNull(3))
                        model.TimeZone = Convert.ToDateTime(item.GetDateTime(3)).ToString("hh:mm tt");
                    if (!item.IsDBNull(4))
                        model.Active = item.GetBoolean(4);

                    list.Add(model);
                }
            }

            return list;
        }

        private List<CalendarEvent> GetCalendarEvents(DateTime startDate, DateTime endDate, string type)
        {
            List<CalendarEvent> list = new List<CalendarEvent>();
            string storeName = string.Empty;

            if (type.ToUpper() == "PRODUCT")
            {
                storeName = "GetAllProductCalendar";
            }
            else
            {
                storeName = "GetAllBTCalendar";
            }

            SqlParameter paramStartDate = new SqlParameter("StartDate", SqlDbType.DateTime);
            SqlParameter paramEndDate = new SqlParameter("EndDate", SqlDbType.DateTime);

            paramStartDate.Value = startDate;
            paramEndDate.Value = endDate;

            // Execute a query
            using (SqlDataReader item = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.StoredProcedure, storeName, paramStartDate, paramEndDate))
            {
                while (item.Read())
                {
                    CalendarEvent model = new CalendarEvent();

                    model.ID = item.GetInt32(0);
                    if(!item.IsDBNull(1))
                        model.Title = item.GetString(1);
                    if (!item.IsDBNull(2))
                        model.Reviewed = item.GetInt32(2);
                    if (!item.IsDBNull(3))
                        model.TimeZone = Convert.ToDateTime(item.GetDateTime(3)).ToString("hh:mm tt");
                    if (!item.IsDBNull(4))
                        model.Active = item.GetBoolean(4);

                    list.Add(model);
                }
            }

            return list;
        }

        // Get product approval by calendar`  
        private List<ProductModel> GetPAList(int pageSize, bool approved)
        {
            List<ProductModel> list = new List<ProductModel>();
            string storeName = string.Empty;
            storeName = "GetAllPA";

            SqlParameter paramPageSize = new SqlParameter("PageSize", SqlDbType.Int);
            SqlParameter paramApproved = new SqlParameter("Approved", SqlDbType.Bit);

            paramPageSize.Value = pageSize;
            paramApproved.Value = approved;

            // Execute a query
            using (SqlDataReader item = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.StoredProcedure, storeName, paramPageSize, paramApproved))
            {
                while (item.Read())
                {
                    ProductModel model = new ProductModel();

                    model.ID = item.GetInt32(0);
                    if (!item.IsDBNull(1))
                        model.Title = item.GetString(1);
                    if (!item.IsDBNull(2))
                        model.Reviewed = item.GetInt32(2);
                    if (!item.IsDBNull(3))
                        model.CreationDate = Convert.ToDateTime(item.GetDateTime(3)).ToString("dd-MM-yyyy hh:mm tt");
                    if (!item.IsDBNull(4))
                        model.Active = item.GetBoolean(4);

                    if (model.Active)
                    {
                        model.ProductState = "Chưa hiển thị";
                    }
                    else
                    {
                        model.ProductState = "Hiển thị";
                    }

                    list.Add(model);
                }
            }

            return list;
        }
        #endregion
    }
}
